*---------------------------------------------VIEW_DATA.DO-----------------------------------------
*This script generates Figures 1, A1, and A2.
*Sebastien Bradley

clear all
capture cd "C:/Users/Sebastien/Documents/Research/PropertyTaxes/Ann_Arbor/AA_proptaxdata19972008"
set matsize 800
capture log close
set more off

log using view_data_results.txt, text replace


use tax_cap_est_data.dta
drop if saleyear==2008

gen I_new = (resyearbuilt==saleyear | resyearbuilt==saleyear-1)

*------------------------------------------------------------------------------------------------------------------------------------------------------
*Compute tax benefit associated with inheriting capped TV for the year in which sale occurs
local av = "sev"

gen tvcapval = (`av'-tv)*mills*d	/*Pro-rated*/
gen d_tax = (`av'-tv)*mills			/*Full-year capped TV tax benefit*/

local indep = "tvcapval"
*------------------------------------------------------------------------------------------------------------------------------------------------------

drop if tvcapval<0

*Apply censoring to distribution of sale prices
summ saleamt, detail
drop if saleamt>500000			/*Excludes top ~5% of transaction amounts*/


/**/
*PLOT PREDICTED AND OBSERVED CHANGES IN TAX LIABILITY BY YEAR
#delimit ;
gen d_tax_liab = (f_tv-tv)*mills;
foreach v in d_tax_liab tvcapval {;
  gen `v'_pct = `v'/saleamt*100;
  gen `v'_pctliab = `v'/(tv*mills)*100;

  foreach vv in `v' `v'_pct `v'_pctliab {;
	gen tmp_`vv' = `vv' if !I_new;
	egen pctile_`vv' = cut(tmp_`vv'), group(100);
  };

};

foreach v in d_tax_liab tvcapval {;  
  preserve;
  collapse (median) tmp_`v'* (mean) avg_`v'=tmp_`v' avg_`v'_pct=tmp_`v'_pct avg_`v'_pctliab=tmp_`v'_pctliab, by(saleyear);
  list;

  gen year1 = saleyear-0.2;
  gen year2 = saleyear+0.2;
  
  if "`v'"=="d_tax_liab" {;
	local titleline = "Median Changes in Tax Liability Following Sale";
  };
  else if "`v'"=="tvcapval" {;
	local titleline = "Pro-rata Capped TV Benefits in Year of Sale";
  };

  if "`v'"=="tvcapval" {;
    /*
	*USED TO PRODUCE OLD FIGURE 2 OF RESTAT R&R;
	twoway (bar avg_`v' year1 if saleyear>=1997 & saleyear<=2007, yaxis(1) color(blue) barw(0.4)) (bar avg_`v'_pct year2 if saleyear>=1997 & saleyear<=2007, yaxis(2) color(sand) barw(0.4)), 
	  legend(label(1 "Dollars") label(2 "Percent") pos(11) ring(0)) ylabel(0(0.05)0.25,axis(2) format(%03.2f))
	  /*title("`titleline'") subtitle("Ann Arbor Sales")*/ ytitle("Dollars", axis(1)) ytitle("Percent of Sale Price", axis(2)) xtitle("Sale Year")
	  xscale(r(1997 2007)) xtick(1999(2)2005) xlabel(1997(2)2007) graphregion(color(white) lstyle(none));
	graph export barchart_avg_`v'_twoway.eps, replace;
	*/
  };

  else if "`v'"=="d_tax_liab" {;
	*USED TO PRODUCE FIGURE 1;
    twoway (bar avg_`v' year1 if saleyear>=1997 & saleyear<=2007, yaxis(1) color(blue) barw(0.4)) (bar avg_`v'_pctliab year2 if saleyear>=1997 & saleyear<=2007, yaxis(2) color(sand) barw(0.4)), 
	  legend(label(1 "Dollars") label(2 "Percent") pos(11) ring(0)) 
	  /*title("`titleline'") subtitle("Ann Arbor Sales")*/ ytitle("Dollars", axis(1)) ytitle("Percent of Pre-Sale Tax Liability", axis(2)) xtitle("Sale Year")
	  xscale(r(1997 2007)) xtick(1999(2)2005) xlabel(1997(2)2007) graphregion(color(white) lstyle(none));
	graph export barchart_avg_`v'_twoway.eps, replace;
	
  };
  restore;
};
/**/


*PLOT MEDIAN MONTHLY SALE PRICES ACROSS YEARS;
*USED TO PRODUCE FIGURE A1;
gen salemnth = mofd(saledate);
format %tm salemnth;
sort salemnth pid saleamt;
by salemnth: egen avg_saleamt = mean(saleamt);				/*All observations*/
by salemnth: egen avg_E_saleamt = mean(E_saleamt_xb);
by salemnth: egen med_saleamt = median(saleamt);
by salemnth: egen med_E_saleamt = median(E_saleamt_xb);
*Normalize HPI to equal Ann Arbor median sale prices in 2000;
gen hpi2000 = enc_hpi if saleyear==2000;
egen avg_hpi2000 = mean(med_saleamt/hpi2000);
gen hpi_norm = enc_hpi*avg_hpi2000;
twoway (histogram salemnth, width(1) freq yaxis(2)) (line med_saleamt salemnth if saleyear>1996 & salemnth~=salemnth[_n-1], yaxis(1)) 
	(line hpi_norm salemnth if saleyear>1996 & salemnth~=salemnth[_n-1], yaxis(1) lcolor(blue) lpattern(dash)), 
	legend(label(1 "Volume") label(2 "Median Price") label(3 "HPI") col(3)) /*title("Monthly Sales Volumes and Prices" "Ann Arbor, 1997-2007")*/ 
	xtitle("Date") ytitle("Number of Sales", axis(2)) ytitle("Dollars", axis(1)) graphregion(color(white) lstyle(none))
	tlabel(1997m1(24)2007m1);
graph export monthly_prices_volume.eps, replace;

#delimit cr


*DEPICT (OVER)CAPITALIZATION OF CAPPED TV BENEFIT INTO SALE PRICES AMONG HOMES BY SEV BINS, # BEDROOMS, SQ. FT., ETC.:
gen bdrms = resnumbed
replace bdrms = 5 if bdrms>5 & bdrms~=.	/*Top code*/

*Use single year to account for year FE (otherwise, large benefits might be correlated with later sales):
*Also, only use full-year capped TV benefit to avoid spurious effect of larger pro-rata benefits occurring at start of year.
local y = 2006
gen d_tax_ratio = 1-(tv/sev) 			/* = (sev-tv)/sev*/
foreach v in sev d_tax d_tax_ratio resfloorarea {
	gen tmp_`v'`y' = `v' if saleyear==`y'
}

disp "YEAR = ",`y'
foreach i in 5 /*10*/ {
	egen pctile`i'_sev`y' = cut(tmp_sev`y'), group(`i')
	egen pctile`i'_sqft`y' = cut(tmp_resfloorarea`y'), group(`i')
	foreach v in d_tax d_tax_ratio {
	  egen pctile`i'_`v'`y' = cut(tmp_`v'`y'), group(`i')

	  table pctile`i'_sev`y' pctile`i'_`v'`y', c(mean saleamt) f(%9.0f)
	  table pctile`i'_sev`y' pctile`i'_`v'`y', c(p50 saleamt) f(%9.0f)
	  table pctile`i'_sev`y' pctile`i'_`v'`y', c(n saleamt) f(%9.0f)
	  table bdrms pctile`i'_`v'`y', c(mean saleamt) f(%9.0f)
	  table bdrms pctile`i'_`v'`y', c(p50 saleamt) f(%9.0f)
	  table bdrms pctile`i'_`v'`y', c(n saleamt) f(%9.0f)
	  table pctile`i'_sqft`y' pctile`i'_`v'`y', c(mean saleamt) f(%9.0f)
	  table pctile`i'_sqft`y' pctile`i'_`v'`y', c(p50 saleamt) f(%9.0f)
	  table pctile`i'_sqft`y' pctile`i'_`v'`y', c(n saleamt) f(%9.0f)
	}
}
tab bdrms if saleyear==`y'

*Focus on quintiles:
bysort pctile5_d_tax_ratio`y': summarize d_tax_ratio if pctile5_d_tax_ratio`y'~=.
bysort pctile5_sqft`y': summarize resfloorarea if pctile5_sqft`y'~=.
table pctile5_sqft`y' pctile5_d_tax_ratio`y', c(mean d_tax p50 d_tax)

*Compute changes in median sale prices and capped TV benefits across benefit quintiles within square footage quintile:
*(Median capped TV benefits are 0 in first quintile for all square footage quintiles)
bysort pctile5_sqft`y' pctile5_d_tax_ratio`y': egen med_d_tax`y' = median(d_tax)
bysort pctile5_sqft`y' pctile5_d_tax_ratio`y': egen med_saleamt`y' = median(saleamt)

gen tmp_q1_med_saleamt`y' = med_saleamt`y' if pctile5_d_tax_ratio`y'==0
bysort pctile5_sqft`y': egen q1_med_saleamt`y' = max(tmp_q1_med_saleamt`y')
gen diff_med_saleamt`y' = med_saleamt`y' - q1_med_saleamt`y'
gen overcap_sqft`y' = diff_med_saleamt`y'/med_d_tax`y'*100


*Plot:
#delimit ;
sort pctile5_sqft`y' pctile5_d_tax_ratio`y' saleamt;
by pctile5_sqft`y' pctile5_d_tax_ratio`y': egen tmp_med_saleamt_by_pctile = median(saleamt);
gen med_saleamt_by_pctile = tmp_med_saleamt_by_pctile if pctile5_d_tax_ratio`y'~=pctile5_d_tax_ratio`y'[_n-1]
	& pctile5_sqft`y'~=. & pctile5_d_tax_ratio`y'~=.;

*USED TO PRODUCE FIGURE A2;	
graph bar (median) saleamt if saleyear==`y', over(pctile5_d_tax_ratio`y', relabel(1 "1st" 2 "2nd" 3 "3rd" 4 "4th" 5 "5th") label(angle(42) labsize(*0.8))) 
	over(pctile5_sqft`y', /*relabel(1 "<1008" 2 "[1008,1248]" 3 "[1250,1529]" 4 "[1530,1972]" 5 ">1972")*/
	relabel(1 "ft`=char(178)'<1008" 2 "[1008,1248]" 3 "[1250,1529]" 4 "[1530,1972]" 5 "1972<ft`=char(178)'") label(labsize(*0.7)))
	ytitle("Median Sale Price", size(*0.85)) ylabel(0(100000)500000, labsize(*0.85))
	/*b1title("Full-Year Capped TV Benefit/SEV Quintile") b2title("Sq. Ft.")*/
	/*text(-40000 50 "Full-Year Capped TV Benefit/SEV Quintile", /*xaxis(2)*/ just(center) size(*0.8))*/
	/*text(-75000 50 "Residential Square Footage Quintile", just(center) size(*0.8))*/
	text(-57000 -11 "ft`=char(178)' Quintile:", just(right) size(*0.8))
	text(-20000 -17 "Benefit/SEV Quintile:", just(right) size(*0.8))
	text(167500 1.5 "0", just(center) size(*0.66))					/*Label median full-year capped TV benefits atop bars for 1st ft2 quintile*/
	text(174000 5 "418", just(center) size(*0.66))					/*Technically, I should probably have different within quintile x quintile measures for all bars*/
	text(182500 8.5 "835", just(center) size(*0.66))
	text(195250 12 "1280", just(center) size(*0.66))
	text(210500 15.5 "1534", just(center) size(*0.66))
	bar(1, color(blue)) graphregion(color(white) lstyle(none) margin(l+18));
graph export barchart_benefit_sqft_quintiles.eps, replace;
sleep 5000;

graph bar (max) overcap_sqft`y' if saleyear==`y', over(pctile5_d_tax_ratio`y', relabel(1 "1st" 2 "2nd" 3 "3rd" 4 "4th" 5 "5th") label(angle(42) labsize(*0.8))) 
	over(pctile5_sqft`y', relabel(1 "ft`=char(178)'<1008" 2 "[1008,1248]" 3 "[1250,1529]" 4 "[1530,1972]" 5 "1972<ft`=char(178)'") label(labsize(*0.7)))
	ytitle("Percent Capitalization of Median Capped TV Benefits", size(*0.85)) ylabel(-4000(2000)4000, labsize(*0.85) format(%9.0g))
	text(-6350 -11 "ft`=char(178)' Quintile:", just(right) size(*0.8))
	text(-5650 -17 "Benefit/SEV Quintile:", just(right) size(*0.8))
	bar(1, color(red)) graphregion(color(white) lstyle(none) margin(l+18));
sleep 5000;
graph export barchart_benefit_sqft_quintiles_overcap.eps, replace;

#delimit cr


log close

/**/
